using Infrastructure;
using Infrastructure.Attribute;
using ZR.Model;
using ZR.Model.System;
using ZR.Model.System.Dto;
using ZR.Repository;
using ZR.ServiceCore.Services.IService;

namespace ZR.ServiceCore.Services
{
    /// <summary>
    /// B_CDWL_PPI_LR服务实现
    /// </summary>
    [AppService(ServiceType = typeof(IB_CDWL_PPI_LRService), ServiceLifetime = LifeTime.Transient)]
    public class B_CDWL_PPI_LRService : BaseService<B_CDWL_PPI_LR>, IB_CDWL_PPI_LRService
    {
        private static readonly object _lockObject = new object();
        private static List<string> _cachedTableNames = null;
        private static DateTime _lastCacheTime = DateTime.MinValue;
        private static readonly TimeSpan _cacheExpiration = TimeSpan.FromMinutes(5); // 缓存5分钟

        /// <summary>
        /// 获取所有B_CDWL_PPI_LR表名列表（包含B_CDWL_PPI_LR的表）
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllB_CDWL_PPI_LRTableNames()
        {
            lock (_lockObject)
            {
                // 检查缓存是否有效
                if (_cachedTableNames != null && DateTime.Now - _lastCacheTime < _cacheExpiration)
                {
                    return _cachedTableNames;
                }

                try
                {
                    // 查询所有包含B_CDWL_PPI_LR的表
                    var allTablesSql = @"
                        SELECT TABLE_NAME 
                        FROM INFORMATION_SCHEMA.TABLES 
                        WHERE TABLE_TYPE = 'BASE TABLE' 
                        AND TABLE_NAME LIKE '%B_CDWL_PPI_LR%'";
                    
                    var allTables = Context.Ado.SqlQuery<string>(allTablesSql).ToList();
                    
                    // 更新缓存
                    _cachedTableNames = allTables;
                    _lastCacheTime = DateTime.Now;
                
                    return allTables;
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"获取B_CDWL_PPI_LR表名列表时出错: {ex.Message}");
                    Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                    return _cachedTableNames ?? new List<string>();
                }
            }
        }

        /// <summary>
        /// 根据时间参数获取对应的B_CDWL_PPI_LR表名列表
        /// </summary>
        /// <param name="timeParam">时间参数</param>
        /// <returns></returns>
        public List<string> GetB_CDWL_PPI_LRTableNames(string timeParam)
        {
            try
            {
                var allTables = GetAllB_CDWL_PPI_LRTableNames();
                
                if (string.IsNullOrEmpty(timeParam))
                {
                    return allTables;
                }

                // 根据时间参数过滤表名
                return allTables.Where(table => table.Contains(timeParam)).ToList();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"根据时间参数获取B_CDWL_PPI_LR表名时出错: {ex.Message}");
                return new List<string>();
            }
        }

        /// <summary>
        /// 获取B_CDWL_PPI_LR查询列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        public PagedInfo<B_CDWL_PPI_LR> SelectB_CDWL_PPI_LRList(B_CDWL_PPI_LRQueryDto query, PagerInfo pager)
        {
            // 根据TimeParam获取包含该时间参数的表名列表
            var sourceTables = GetB_CDWL_PPI_LRTableNames(query.TimeParam);

            if (!sourceTables.Any())
            {
                return new PagedInfo<B_CDWL_PPI_LR>
                {
                    Result = new List<B_CDWL_PPI_LR>(),
                    TotalNum = 0,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }

            // 如果指定了特定表，直接查询该表（query.SourceTable为实际表名）
            if (!string.IsNullOrEmpty(query.SourceTable))
            {
                return GetDataFromTableWithPaging(query.SourceTable, query, pager);
            }

            // 多表查询优化：使用UNION ALL合并查询，在数据库层面进行分页
            return GetDataFromMultipleTablesWithPaging(sourceTables, query, pager);
        }

        /// <summary>
        /// 从指定表获取数据（带分页）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        private PagedInfo<B_CDWL_PPI_LR> GetDataFromTableWithPaging(string tableName, B_CDWL_PPI_LRQueryDto query, PagerInfo pager)
        {
            try
            {
                // 构建查询SQL，包含分页
                var sql = BuildSelectSqlWithPaging(tableName, query, pager);
                Console.WriteLine($"B_CDWL_PPI_LR查询 - 执行SQL: {sql}");
                
                // 构建参数对象
                var parameters = new
                {
                    id = query.Id,
                    timestamp = query.Timestamp,
                    azimuth = query.Azimuth,
                    elevation = query.Elevation,
                    distance = query.Distance,
                    radialSpeed = query.RadialSpeed,
                    spectrumWidth = query.SpectrumWidth,
                    snr = query.Snr,
                    amplitude = query.Amplitude,
                    beginTime = query.BeginTime,
                    endTime = query.EndTime,
                    offset = (pager.PageNum - 1) * pager.PageSize,
                    limit = pager.PageSize
                };
                
                // 查询数据
                var result = Context.Ado.SqlQuery<B_CDWL_PPI_LR>(sql, parameters);
                
                // 查询总数
                var countSql = BuildCountSql(tableName, query);
                var totalCountLong = Context.Ado.SqlQuery<long>(countSql, new
                {
                    id = query.Id,
                    timestamp = query.Timestamp,
                    azimuth = query.Azimuth,
                    elevation = query.Elevation,
                    distance = query.Distance,
                    radialSpeed = query.RadialSpeed,
                    spectrumWidth = query.SpectrumWidth,
                    snr = query.Snr,
                    amplitude = query.Amplitude,
                    beginTime = query.BeginTime,
                    endTime = query.EndTime
                }).FirstOrDefault();
                var totalCount = totalCountLong > int.MaxValue ? int.MaxValue : (int)totalCountLong;
                
                Console.WriteLine($"B_CDWL_PPI_LR查询 - SQL查询返回行数: {result.Count}, 总数: {totalCount}");
                
                // 设置SourceTable字段
                var ppiList = result.Select(item => new B_CDWL_PPI_LR
                {
                    Id = item.Id,
                    Timestamp = item.Timestamp,
                    Azimuth = item.Azimuth,
                    Elevation = item.Elevation,
                    Distance = item.Distance,
                    RadialSpeed = item.RadialSpeed,
                    SpectrumWidth = item.SpectrumWidth,
                    Snr = item.Snr,
                    Amplitude = item.Amplitude,
                    SourceTable = tableName
                }).ToList();

                return new PagedInfo<B_CDWL_PPI_LR>
                {
                    Result = ppiList,
                    TotalNum = totalCount,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询表 {tableName} 时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                return new PagedInfo<B_CDWL_PPI_LR>
                {
                    Result = new List<B_CDWL_PPI_LR>(),
                    TotalNum = 0,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
        }

        /// <summary>
        /// 从多个表获取数据（带分页）
        /// </summary>
        /// <param name="tableNames">表名列表</param>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        private PagedInfo<B_CDWL_PPI_LR> GetDataFromMultipleTablesWithPaging(List<string> tableNames, B_CDWL_PPI_LRQueryDto query, PagerInfo pager)
        {
            try
            {
                // 如果只有一张表，直接走单表分页逻辑，返回该表的 Result 和 TotalNum
                if (tableNames != null && tableNames.Count == 1)
                {
                    return GetDataFromTableWithPaging(tableNames[0], query, pager);
                }

                // 计算每个表的总数，并聚合总数
                var tableCounts = new List<(string Table, int Count)>();
                var totalCountAllTables = 0;
                foreach (var tableName in tableNames)
                {
                    try
                    {
                        var countSql = BuildCountSql(tableName, query);
                        var tableTotal = Context.Ado.SqlQuery<long>(countSql, new
                        {
                            id = query.Id,
                            timestamp = query.Timestamp,
                            azimuth = query.Azimuth,
                            elevation = query.Elevation,
                            distance = query.Distance,
                            radialSpeed = query.RadialSpeed,
                            spectrumWidth = query.SpectrumWidth,
                            snr = query.Snr,
                            amplitude = query.Amplitude,
                            beginTime = query.BeginTime,
                            endTime = query.EndTime
                        }).FirstOrDefault();
                        tableCounts.Add((tableName, (int)tableTotal));
                        totalCountAllTables += (int)tableTotal;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"统计表 {tableName} 总数时出错: {ex.Message}");
                    }
                }

                // 计算当前页需要的数据区间
                var startIndex = (pager.PageNum - 1) * pager.PageSize;
                var remaining = pager.PageSize;
                var results = new List<B_CDWL_PPI_LR>();

                // 按表名排序，保证与最终排序一致（SourceTable, Id）
                foreach (var (table, count) in tableCounts.OrderBy(t => t.Table))
                {
                    if (remaining <= 0)
                    {
                        break;
                    }

                    if (count <= 0)
                    {
                        continue;
                    }

                    if (startIndex >= count)
                    {
                        startIndex -= count; // 跳过当前表
                        continue;
                    }

                    var offsetInTable = startIndex;
                    var takeFromThisTable = Math.Min(remaining, count - offsetInTable);

                    var segment = GetDataFromTableSegment(table, query, offsetInTable, takeFromThisTable);
                    // segment 已按 id 排序，表间按表名顺序拼接可保持 (SourceTable, Id) 排序
                    results.AddRange(segment);

                    remaining -= takeFromThisTable;
                    startIndex = 0; // 之后的表从起始位置开始
                }

                // 如果按精确分段未取到数据但总数>0，回退到有限表聚合分页以保证可用性（尤其是第一页）
                if (results.Count == 0 && totalCountAllTables > 0)
                {
                    var limitedTables = tableCounts.Select(t => t.Table).OrderBy(n => n).Take(5).ToList();
                    var temp = new List<B_CDWL_PPI_LR>();
                    foreach (var table in limitedTables)
                    {
                        try
                        {
                            var tableData = GetDataFromTableWithPaging(table, query, new PagerInfo { PageNum = 1, PageSize = pager.PageSize * 2 });
                            if (tableData.Result != null)
                            {
                                temp.AddRange(tableData.Result);
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"回退模式查询表 {table} 时出错: {ex.Message}");
                        }
                    }
                    results = temp.OrderBy(x => x.SourceTable).ThenBy(x => x.Id)
                        .Skip((pager.PageNum - 1) * pager.PageSize)
                        .Take(pager.PageSize)
                        .ToList();
                }

                Console.WriteLine($"B_CDWL_PPI_LR多表查询 - 返回行数: {results.Count}, 总数(所有表): {totalCountAllTables}");

                return new PagedInfo<B_CDWL_PPI_LR>
                {
                    Result = results,
                    TotalNum = totalCountAllTables,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
            catch (Exception ex)
            {
                Console.WriteLine($"多表查询时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                return new PagedInfo<B_CDWL_PPI_LR>
                {
                    Result = new List<B_CDWL_PPI_LR>(),
                    TotalNum = 0,
                    PageIndex = pager.PageNum,
                    PageSize = pager.PageSize
                };
            }
        }

        /// <summary>
        /// 从指定表获取特定偏移和条数的数据段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <param name="offset">偏移量</param>
        /// <param name="limit">条数</param>
        /// <returns></returns>
        private List<B_CDWL_PPI_LR> GetDataFromTableSegment(string tableName, B_CDWL_PPI_LRQueryDto query, int offset, int limit)
        {
            try
            {
                var selectFields = "id, timestamp, azimuth, elevation, distance, radial_speed, spectrum_width, snr, amplitude";
                var whereConditions = BuildWhereConditions(query);
                var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
                var sql = $"SELECT {selectFields} FROM {tableName} {whereClause} ORDER BY id LIMIT @limit OFFSET @offset";

                var parameters = new
                {
                    id = query.Id,
                    timestamp = query.Timestamp,
                    azimuth = query.Azimuth,
                    elevation = query.Elevation,
                    distance = query.Distance,
                    radialSpeed = query.RadialSpeed,
                    spectrumWidth = query.SpectrumWidth,
                    snr = query.Snr,
                    amplitude = query.Amplitude,
                    beginTime = query.BeginTime,
                    endTime = query.EndTime,
                    offset = offset,
                    limit = limit
                };

                var result = Context.Ado.SqlQuery<B_CDWL_PPI_LR>(sql, parameters);

                return result.Select(item => new B_CDWL_PPI_LR
                {
                    Id = item.Id,
                    Timestamp = item.Timestamp,
                    Azimuth = item.Azimuth,
                    Elevation = item.Elevation,
                    Distance = item.Distance,
                    RadialSpeed = item.RadialSpeed,
                    SpectrumWidth = item.SpectrumWidth,
                    Snr = item.Snr,
                    Amplitude = item.Amplitude,
                    SourceTable = tableName
                }).ToList();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询表 {tableName} 段数据时出错: {ex.Message}");
                return new List<B_CDWL_PPI_LR>();
            }
        }

        /// <summary>
        /// 构建带分页的查询SQL
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        private string BuildSelectSqlWithPaging(string tableName, B_CDWL_PPI_LRQueryDto query, PagerInfo pager)
        {
            var selectFields = "id, timestamp, azimuth, elevation, distance, radial_speed, spectrum_width, snr, amplitude";
            var whereConditions = BuildWhereConditions(query);

            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            return $"SELECT {selectFields} FROM {tableName} {whereClause} ORDER BY id LIMIT @limit OFFSET @offset";
        }

        /// <summary>
        /// 构建计数SQL
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private string BuildCountSql(string tableName, B_CDWL_PPI_LRQueryDto query)
        {
            var whereConditions = BuildWhereConditions(query);
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            return $"SELECT COUNT(1) FROM {tableName} {whereClause}";
        }

        /// <summary>
        /// 构建WHERE条件
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private List<string> BuildWhereConditions(B_CDWL_PPI_LRQueryDto query)
        {
            var whereConditions = new List<string>();

            // 添加过滤条件（使用参数化查询避免SQL注入）
            if (query.Id.HasValue)
            {
                whereConditions.Add($"id = @id");
            }

            if (!string.IsNullOrEmpty(query.Timestamp))
            {
                if (query.UseLike)
                {
                    whereConditions.Add($"timestamp LIKE CONCAT('%', @timestamp, '%')");
                }
                else
                {
                    whereConditions.Add($"timestamp = @timestamp");
                }
            }

            if (query.Azimuth.HasValue)
            {
                whereConditions.Add($"azimuth = @azimuth");
            }

            if (query.Elevation.HasValue)
            {
                whereConditions.Add($"elevation = @elevation");
            }

            if (query.Distance.HasValue)
            {
                whereConditions.Add($"distance = @distance");
            }

            if (query.RadialSpeed.HasValue)
            {
                whereConditions.Add($"radial_speed = @radialSpeed");
            }

            if (query.SpectrumWidth.HasValue)
            {
                whereConditions.Add($"spectrum_width = @spectrumWidth");
            }

            if (query.Snr.HasValue)
            {
                whereConditions.Add($"snr = @snr");
            }

            if (query.Amplitude.HasValue)
            {
                whereConditions.Add($"amplitude = @amplitude");
            }

            // 时间范围过滤
            if (query.BeginTime.HasValue)
            {
                whereConditions.Add("timestamp >= @beginTime");
            }

            if (query.EndTime.HasValue)
            {
                whereConditions.Add("timestamp <= @endTime");
            }

            // 添加数据有效性筛选条件 - 过滤掉无效的radial_speed值
            whereConditions.Add("radial_speed != '-9999.0000'");

            return whereConditions;
        }

        /// <summary>
        /// 清理表名缓存
        /// </summary>
        public static void ClearTableNameCache()
        {
            lock (_lockObject)
            {
                _cachedTableNames = null;
                _lastCacheTime = DateTime.MinValue;
            }
        }
    }
}

